<?php
// SimpleTable
// Alex McClung
// Basic table class. Represents a table in a MySQL database.
class SimpleTable
{
	//===================================================================================
	// Private Variables
	private $_sql;         // SQL class instance
	private $_primary_key; // Name of the primary key column
	
	//===================================================================================
	// Public Variables
	public $name;      // The table's name
	public $type;      // MySQL table type
	public $comment;   // Table comments
	public $indexes;   // Array of names of index columns
	public $fulltexts; // Array of names of fulltext columns
	public $uniques;   // Array of names of unique columns
	public $columns;   // Array of SimpleColumns
	
	//===================================================================================
	// Public Functions
	
	//-----------------------------------------------------------------------------------
	// Class constructor
	// $name (String) (Optional) The name of the table. If supplied, the table will attempt
	//                           to load table data from the database by searching for a
	//                           table named $name.
	public function __construct($name=NULL)
	{
		// Use a global variable to store the SQL connection so we don't have
		// to reconnect every time the class is instantiated.
		if(!isset($GLOBALS['_dbo_sql']))
			$this->Reconnect();
		else
			$this->_sql = $GLOBALS['_dbo_sql'];
		
		// Initialize arrays
		$this->columns   = array();
		$this->indexes   = array();
		$this->fulltexts = array();
		$this->uniques   = array();
		
		// Did they supply a table name?
		if($name != NULL)
		{
			// Attempt to look-up the table info
			$this->Lookup($name);
		}
	}
	
	//-----------------------------------------------------------------------------------
	// (Re)connects to the database
	public function Reconnect()
	{
		$this->_sql = new SQL();
		$GLOBALS['_dbo_sql'] = $this->_sql;
	}
	
	//-----------------------------------------------------------------------------------
	// Looks up information on a table in the database.
	// $tablename (String) Name of the table to get data for
	public function Lookup($tablename)
	{
		// Does this table even exist?
		if(!$this->_sql->TableExists($tablename))
		{
			return;
		}
		
		// The table exists, start pulling information
		$this->name = $tablename;
		
		// TODO: Lookup the table's comments
		
		// Load column info (yes, this is built into the base SQL class,
		// but I don't want to parse info twice)
		$columns = $this->_sql->Query("SHOW COLUMNS FROM `{$this->name}`");
		while( $row = $this->_sql->GetObject($columns) )
		{
			list($type, $fieldinfo, $attributes) = $this->_sql->GetTypeInfo($row->Type);
			
			$col = new SimpleColumn();
			$col->name           = $row->Field;
			$col->type           = $type;
			$col->fieldinfo      = $fieldinfo;
			$col->attributes     = $attributes;
			$col->null           = $row->Null;
			$col->default        = $row->Default;
			$col->auto_increment = ( strncasecmp($row->Extra, "auto_increment", strlen("auto_increment") ) == 0);
			
			// Do some cleanup before we do the next bit of querying
			unset($type, $fieldinfo, $attributes, $row);
			
			// Figure out what indexes and keys this column has on it
			$keys = $this->_sql->Query("SHOW KEYS FROM `{$this->name}` WHERE Column_name='{$col->name}'");
			while( $key = $this->_sql->GetObject($keys) )
			{
				     if( $key->Index_type == "FULLTEXT" ) { $col->fulltext = true;	  }
				else if( $key->Key_name   == "PRIMARY"  ) {	$col->primary_key = true; }
				else if( $key->Non_unique == 0          ) {	$col->unique = true;      }
				else                                   	  {	$col->index = true;	   	  }
			}
			
			// Cleanup the query
			$this->_sql->Free($keys);
			unset($key, $keys);
			
			// Add the column to this table
			$this->AddColumn($col);
		}
		
		// Final cleanup! We're done!
		$this->_sql->Free($columns);
		unset($columns);
	}
	
	//-----------------------------------------------------------------------------------
	// Adds a column to the table
	public function AddColumn($column)
	{
		// Add the column to the list
		$this->columns[] = $column;
		
		// Add the column name to the various lists
		if($column->fulltext)
			$this->fulltexts[]  = $column->name;
		if($column->unique)
			$this->uniques[]    = $column->name;
		if($column->index)
			$this->indexes[]    = $column->name;
		if($column->primary_key)
			$this->_primary_key = $column->name;
	}
	
	//-----------------------------------------------------------------------------------
	// Creates the table
	public function Create()
	{
		$syntax = "CREATE TABLE `{$this->name}` (\n";
		foreach($this->columns as $column)
		{
			$fieldInfo = "";
			if($column->type == "ENUM" || $column->type == "LIST")
			{
				$fieldInfo = "(" . stripslashes($column->fieldinfo) . ")";
			}
			else
			{
				$fieldInfo = (strlen($column->fieldinfo) > 0) ? "({$column->fieldinfo})" : "";
			}
			$default   = (strlen($column->default)   > 0) ? "DEFAULT '{$column->default})" : "";
			$autoInc   = ($column->auto_increment) ? "AUTO_INCREMENT" : "";
			$syntax .= "	`{$column->name}` {$column->type}$fieldInfo {$column->attributes} {$column->null} $default  $autoInc,\n";
		}
		if(count($this->indexes) > 0)
			$syntax .= "	INDEX (`" . implode("`,`", $this->indexes) . "`),\n";
		if(strlen($this->_primary_key) > 0)
			$syntax .= "	PRIMARY KEY (`{$this->_primary_key}`),\n";
		if(count($this->fulltexts) > 0)
			$syntax .= "	FULLTEXT (`" . implode("`,`", $this->fulltexts) . "`),\n";
		if(count($this->uniques) > 0)
			$syntax .= "	UNIQUE (`" . implode("`,`", $this->uniques) . "`),\n";
		$syntax = trim($syntax, ",\n");
		$type = (strlen($this->type) > 0) ? "TYPE=`{$this->type}`" : "";
		$syntax .= "\n) $type COMMENT='{$this->comment}';";
		return $this->_sql->Query($syntax);
	}
	
	//-----------------------------------------------------------------------------------
	// Updates the table
	public function Update()
	{
		// Add a new column (end of table)
		// ALTER TABLE `person` ADD `newfield` VARCHAR( 255 ) NOT NULL ;
		
		// Add a new column (front of table)
		// ALTER TABLE `person` ADD `yes` VARCHAR( 2 ) NOT NULL FIRST ;
		
		// Add a new column (after an existing column)
		// ALTER TABLE `person` ADD `jjj` VARCHAR( 2 ) NOT NULL AFTER `{$colname}` ;
		
		// Alter existing column (rename id to id5, change field length to 100)
		// ALTER TABLE `person` CHANGE `id` `id5` INT( 100 ) UNSIGNED NOT NULL AUTO_INCREMENT 
		
		// Change primary key
		// ALTER TABLE `person` DROP PRIMARY KEY, ADD PRIMARY KEY (`{$colname}`)
		
		// Add new index
		// ALTER TABLE `person` ADD INDEX ( `yes` ) 
		
		// Add new unique field
		// ALTER TABLE `person` ADD UNIQUE ( `yes` ) 
		
		// Delete column
		// ALTER TAPLE `person` DROP `jjj`
	}

	//-----------------------------------------------------------------------------------
	// Removes the table
	public function Remove()
	{
		return $this->_sql->Query("DROP TABLE `{$this->name}`");
	}
	
	//-----------------------------------------------------------------------------------
	// Retrieves the last error generated by MySQL
	public function LastError()
	{
		return $this->_sql->LastError();
	}

	//===================================================================================
	// Private Functions
	
	//===================================================================================
	// Accessors
	
	//-----------------------------------------------------------------------------------
	// Special accessor method for retrieving speciailized data as a class property
	public function __get($varname)
	{
		return $this->$varname;
	}
	
	//-----------------------------------------------------------------------------------
	// Special accessor method for assigning speciailized data as a class property
	public function __set($varname, $value)
	{
		$this->$varname = $value;
	}
}
?>